---
title: "SqlServer中的HierarchyId数据类型"
date: 2016
categories:
- mssql
tags:
---

<div id="content">
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#orgdaddb51">Query Example</a>
<ul>
<li><a href="#orgc8bc6e8">基本查询，ToString函数</a></li>
<li><a href="#org010907b">通过HierarchyId值查询数据</a></li>
<li><a href="#org1cfff7f">获取上级结点函数</a></li>
<li><a href="#orgdfd9539">获取子结点</a></li>
<li><a href="#org7bd24f2">获取直接下级</a></li>
</ul>
</li>
<li><a href="#org8115951">Insert Example</a>
<ul>
<li><a href="#orgf62edf4">创建新数据库和测试数据</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div class="outline-2" id="outline-container-orgdaddb51">
<h2 id="orgdaddb51">Query Example</h2>
<div class="outline-text-2" id="text-orgdaddb51">
<p>
查询数据库为AdventureWorks2008R2_Data: <a href="http://msftdbprodsamples.codeplex.com/releases/view/59211">http://msftdbprodsamples.codeplex.com/releases/view/59211</a>
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold; font-style: italic;">-- 一共290条记录</span>
<span style="font-weight: bold;">select</span> <span style="font-weight: bold;">count</span>(*) <span style="font-weight: bold;">from</span> HumanResources.Employee;
</pre>
</div>
</div>
<div class="outline-3" id="outline-container-orgc8bc6e8">
<h3 id="orgc8bc6e8">基本查询，ToString函数</h3>
<div class="outline-text-3" id="text-orgc8bc6e8">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold; font-style: italic;">--HumanResources.Employee.OrganizationLevel列的</span>
<span style="font-weight: bold; font-style: italic;">--计算列规范为([OrganizationNode].[GetLevel]())</span>
<span style="font-weight: bold;">select</span> 
e.businessEntityID,
p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
e.OrganizationNode.ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> person.person p <span style="font-weight: bold;">on</span> e.BusinessEntityID = p.BusinessEntityID
<span style="font-weight: bold;">where</span> e.OrganizationLevel <span style="font-weight: bold;">Between</span> 1 <span style="font-weight: bold;">and</span> 2
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org010907b">
<h3 id="org010907b">通过HierarchyId值查询数据</h3>
<div class="outline-text-3" id="text-org010907b">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">declare</span> @hierarchyValue HierarchyId
<span style="font-weight: bold;">select</span> @hierarchyValue=e.OrganizationNode <span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">where</span> e.businessEntityID=234;
<span style="font-weight: bold;">select</span> * <span style="font-weight: bold;">from</span> HumanResources.Employee <span style="font-weight: bold;">where</span> HumanResources.Employee.OrganizationNode=@hierarchyValue;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org1cfff7f">
<h3 id="org1cfff7f">获取上级结点函数</h3>
<div class="outline-text-3" id="text-org1cfff7f">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">select</span> e.BusinessEntityID,
p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
e.OrganizationNode.ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>,
e.OrganizationNode.GetAncestor(0).ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Self</span>,
e.OrganizationNode.GetAncestor(1).ToString() <span style="font-weight: bold;">as</span> OneUp,
e.OrganizationNode.GetAncestor(2).ToString() <span style="font-weight: bold;">as</span> TwoUp,
e.OrganizationNode.GetAncestor(3).ToString() <span style="font-weight: bold;">as</span> TooFar
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> Person.Person p
<span style="font-weight: bold;">on</span> e.BusinessEntityID=p.BusinessEntityID
<span style="font-weight: bold;">where</span> e.BusinessEntityID=3;

<span style="font-weight: bold; font-style: italic;">--获取person Tamburello的所有上级主管（获取所有上级节点）</span>
<span style="font-weight: bold; font-style: italic;">--childNode.IsDescendantOf(parentNode) 判断childNode是否为parentNode的子节点</span>
<span style="font-weight: bold; font-style: italic;">--查询结果会包括Tamburello自身。</span>
<span style="font-weight: bold; font-style: italic;">--因为一个几点可以认为自己既是自己的上级节点（层级为），也能是自己的子节点。</span>
<span style="font-weight: bold;">declare</span> @ChildNode HierarchyID
<span style="font-weight: bold;">select</span> @ChildNode=OrganizationNode
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">where</span> e.BusinessEntityID=3

<span style="font-weight: bold;">select</span> e.BusinessEntityID,
        p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
        e.OrganizationNode.ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> Person.Person p
        <span style="font-weight: bold;">on</span> e.BusinessEntityID=p.BusinessEntityID
<span style="font-weight: bold;">where</span> @ChildNode.IsDescendantOf(e.OrganizationNode)=1;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-orgdfd9539">
<h3 id="orgdfd9539">获取子结点</h3>
<div class="outline-text-3" id="text-orgdfd9539">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">declare</span> @ChildNode HierarchyID
<span style="font-weight: bold;">select</span> @ChildNode=OrganizationNode
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">where</span> e.BusinessEntityID=3

<span style="font-weight: bold;">select</span> e.BusinessEntityID,
p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
e.OrganizationNode.ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>
<span style="font-weight: bold;">From</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> Person.Person p
<span style="font-weight: bold;">on</span> e.BusinessEntityID=p.BusinessEntityID
<span style="font-weight: bold;">where</span> e.OrganizationNode.IsDescendantOf(@ChildNode)=1;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org7bd24f2">
<h3 id="org7bd24f2">获取直接下级</h3>
<div class="outline-text-3" id="text-org7bd24f2">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">declare</span> @ChildNode hierarchyID
<span style="font-weight: bold;">select</span> @ChildNode=OrganizationNode
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">where</span> e.BusinessEntityID=3;

<span style="font-weight: bold;">select</span> e.BusinessEntityID,
        <span style="font-weight: bold;">Left</span>((p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName),30) <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
        <span style="font-weight: bold;">Left</span>(e.OrganizationNode.ToString(),10) <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>
<span style="font-weight: bold;">From</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> Person.Person p
<span style="font-weight: bold;">on</span> e.BusinessEntityID=p.BusinessEntityId
<span style="font-weight: bold;">where</span> e.OrganizationNode.GetAncestor(1)=@ChildNode;
</pre>
</div>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org8115951">
<h2 id="org8115951">Insert Example</h2>
<div class="outline-text-2" id="text-org8115951">
</div>
<div class="outline-3" id="outline-container-orgf62edf4">
<h3 id="orgf62edf4">创建新数据库和测试数据</h3>
<div class="outline-text-3" id="text-orgf62edf4">
<div class="org-src-container">
<pre class="src src-sql">use [Test]
<span style="font-weight: bold;">create</span> <span style="font-weight: bold;">table</span> <span style="font-weight: bold;">NodeTest</span>(
NodeID <span style="font-weight: bold; text-decoration: underline;">int</span> <span style="font-weight: bold;">not</span> <span style="font-weight: bold;">null</span> <span style="font-weight: bold;">identity</span>(1,1) <span style="font-weight: bold;">primary</span> <span style="font-weight: bold;">key</span>,
Node hierarchyid <span style="font-weight: bold;">not</span> <span style="font-weight: bold;">null</span>,
NodeLevel <span style="font-weight: bold;">as</span> Node.GetLevel(),
<span style="font-weight: bold;">Name</span> <span style="font-weight: bold; text-decoration: underline;">varchar</span>(50) <span style="font-weight: bold;">not</span> <span style="font-weight: bold;">null</span>
)

<span style="font-weight: bold; font-style: italic;">--插入根节点，新的id为</span>
<span style="font-weight: bold;">insert</span> NodeTest <span style="font-weight: bold;">values</span>(<span style="font-style: italic;">'/'</span>,<span style="font-style: italic;">'Manager'</span>);
<span style="font-weight: bold; font-style: italic;">--获取id为的数据（获取根节点）</span>
<span style="font-weight: bold;">declare</span> @Manager hierarchyid
<span style="font-weight: bold;">select</span> @Manager = node <span style="font-weight: bold;">from</span> NodeTest <span style="font-weight: bold;">where</span> NodeId=1;
<span style="font-weight: bold;">insert</span> NodeTest <span style="font-weight: bold;">values</span>
(@manager.GetDescendant(<span style="font-weight: bold;">null</span>,<span style="font-weight: bold;">null</span>),<span style="font-style: italic;">'under root 1'</span>),<span style="font-weight: bold; font-style: italic;">--直接放置在根节点下</span>
(@manager.GetDescendant(<span style="font-weight: bold;">null</span>,<span style="font-weight: bold;">null</span>),<span style="font-style: italic;">'under root 2'</span>),
(@manager.GetDescendant(<span style="font-weight: bold;">null</span>,<span style="font-style: italic;">'/1000/'</span>),<span style="font-style: italic;">'less than 1000 1'</span>),<span style="font-weight: bold; font-style: italic;">--放置在根节点下小于的一个节点</span>
(@manager.GetDescendant(<span style="font-weight: bold;">null</span>,<span style="font-style: italic;">'/1000/'</span>),<span style="font-style: italic;">'less than 1000 2'</span>),<span style="font-weight: bold; font-style: italic;">--放置在根节点下小于的一个节点</span>
(@manager.GetDescendant(<span style="font-style: italic;">'/1000/'</span>,<span style="font-weight: bold;">null</span>),<span style="font-style: italic;">'more than 1000 1'</span>),<span style="font-weight: bold; font-style: italic;">--放置在根节点下大于的一个节点</span>
(@manager.GetDescendant(<span style="font-style: italic;">'/1000/'</span>,<span style="font-weight: bold;">null</span>),<span style="font-style: italic;">'more than 1000 2'</span>),<span style="font-weight: bold; font-style: italic;">--放置在根节点下大于的一个节点</span>
(<span style="font-style: italic;">'/547/'</span>,<span style="font-style: italic;">'ReportFFF'</span>),
(@manager.GetDescendant(<span style="font-style: italic;">'/3/'</span>,<span style="font-style: italic;">'/547/'</span>),<span style="font-style: italic;">'3 - 547'</span>),
(@manager.GetDescendant(<span style="font-style: italic;">'/1/'</span>,<span style="font-style: italic;">'/2/'</span>),<span style="font-style: italic;">'more than 1000 2'</span>),
(@manager.GetDescendant(<span style="font-style: italic;">'/-10/'</span>,<span style="font-style: italic;">'/-1/'</span>),<span style="font-style: italic;">'more than 1000 2'</span>),
(<span style="font-style: italic;">'/547/345/'</span>,<span style="font-style: italic;">'secondLevel AA'</span>),
(<span style="font-style: italic;">'/547/346/'</span>,<span style="font-style: italic;">'secondLevel BB'</span>),
(<span style="font-style: italic;">'/547/345/1/'</span>,<span style="font-style: italic;">'ThirdLevel AA'</span>),
(<span style="font-style: italic;">'/785/294/386/925/'</span>,<span style="font-style: italic;">'RandomEntry'</span>);
</pre>
</div>
<p>
结果：
</p>
<table border="2" cellpadding="6" cellspacing="0" frame="hsides" rules="groups">
<colgroup>
<col class="org-right"/>
<col class="org-left"/>
<col class="org-right"/>
<col class="org-left"/>
</colgroup>
<tbody>
<tr>
<td class="org-right">1</td>
<td class="org-left">/</td>
<td class="org-right">0</td>
<td class="org-left">Manager</td>
</tr>
<tr>
<td class="org-right">2</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 1</td>
</tr>
<tr>
<td class="org-right">3</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 2</td>
</tr>
<tr>
<td class="org-right">4</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 1</td>
</tr>
<tr>
<td class="org-right">5</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 2</td>
</tr>
<tr>
<td class="org-right">6</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 1</td>
</tr>
<tr>
<td class="org-right">7</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">8</td>
<td class="org-left"><i>547</i></td>
<td class="org-right">1</td>
<td class="org-left">ReportFFF</td>
</tr>
<tr>
<td class="org-right">9</td>
<td class="org-left"><i>4</i></td>
<td class="org-right">1</td>
<td class="org-left">3 - 547</td>
</tr>
<tr>
<td class="org-right">10</td>
<td class="org-left"><i>1.1</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">11</td>
<td class="org-left"><i>-9</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">12</td>
<td class="org-left"><i>547/345</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel AA</td>
</tr>
<tr>
<td class="org-right">13</td>
<td class="org-left"><i>547/346</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel BB</td>
</tr>
<tr>
<td class="org-right">14</td>
<td class="org-left"><i>547/345/1</i></td>
<td class="org-right">3</td>
<td class="org-left">ThirdLevel AA</td>
</tr>
<tr>
<td class="org-right">15</td>
<td class="org-left"><i>785/294/386/925</i></td>
<td class="org-right">4</td>
<td class="org-left">RandomEntry</td>
</tr>
</tbody>
</table>
<p>
执行了去除重复的移动操作，结果如下，（只有两个1001节点，两个1001节点是手动插入的数据）
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">update</span> NodeTest
<span style="font-weight: bold;">set</span> Node=Node.GetReparentedValue(<span style="font-style: italic;">'/547/'</span>,<span style="font-style: italic;">'/1001/'</span>)
<span style="font-weight: bold;">where</span> Node.IsDescendantOf(<span style="font-style: italic;">'/547/'</span>)=1
<span style="font-weight: bold;">select</span> Node.ToString() <span style="font-weight: bold;">from</span> NodeTest;
</pre>
</div>
<table border="2" cellpadding="6" cellspacing="0" frame="hsides" rules="groups">
<colgroup>
<col class="org-right"/>
<col class="org-left"/>
<col class="org-right"/>
<col class="org-left"/>
</colgroup>
<tbody>
<tr>
<td class="org-right">1</td>
<td class="org-left">/</td>
<td class="org-right">0</td>
<td class="org-left">Manager</td>
</tr>
<tr>
<td class="org-right">2</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 1</td>
</tr>
<tr>
<td class="org-right">3</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 2</td>
</tr>
<tr>
<td class="org-right">4</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 1</td>
</tr>
<tr>
<td class="org-right">5</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 2</td>
</tr>
<tr>
<td class="org-right">6</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 1</td>
</tr>
<tr>
<td class="org-right">7</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">8</td>
<td class="org-left"><i>547</i></td>
<td class="org-right">1</td>
<td class="org-left">ReportFFF</td>
</tr>
<tr>
<td class="org-right">9</td>
<td class="org-left"><i>4</i></td>
<td class="org-right">1</td>
<td class="org-left">3 - 547</td>
</tr>
<tr>
<td class="org-right">10</td>
<td class="org-left"><i>1.1</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">11</td>
<td class="org-left"><i>-9</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">12</td>
<td class="org-left"><i>1001/345</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel AA</td>
</tr>
<tr>
<td class="org-right">13</td>
<td class="org-left"><i>1001/346</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel BB</td>
</tr>
<tr>
<td class="org-right">14</td>
<td class="org-left"><i>1001/345/1</i></td>
<td class="org-right">3</td>
<td class="org-left">ThirdLevel AA</td>
</tr>
<tr>
<td class="org-right">15</td>
<td class="org-left"><i>785/294/386/925</i></td>
<td class="org-right">4</td>
<td class="org-left">RandomEntry</td>
</tr>
</tbody>
</table>
<p>
如果执行不去除重复的移动操作，结果如下
</p>
<p>
注意有3个1001节点，其中两个是手动插入的节点，另外一个是547节点移动后变成为了1001
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">update</span> NodeTest
<span style="font-weight: bold;">set</span> Node=Node.GetReparentedValue(<span style="font-style: italic;">'/547/'</span>,<span style="font-style: italic;">'/1001/'</span>)
<span style="font-weight: bold;">where</span> Node.IsDescendantOf(<span style="font-style: italic;">'/547/'</span>)=1
<span style="font-weight: bold;">select</span> Node.ToString() <span style="font-weight: bold;">from</span> NodeTest;
</pre>
</div>
<table border="2" cellpadding="6" cellspacing="0" frame="hsides" rules="groups">
<colgroup>
<col class="org-right"/>
<col class="org-left"/>
<col class="org-right"/>
<col class="org-left"/>
</colgroup>
<tbody>
<tr>
<td class="org-right">1</td>
<td class="org-left">/</td>
<td class="org-right">0</td>
<td class="org-left">Manager</td>
</tr>
<tr>
<td class="org-right">2</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 1</td>
</tr>
<tr>
<td class="org-right">3</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 2</td>
</tr>
<tr>
<td class="org-right">4</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 1</td>
</tr>
<tr>
<td class="org-right">5</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 2</td>
</tr>
<tr>
<td class="org-right">6</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 1</td>
</tr>
<tr>
<td class="org-right">7</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">8</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">ReportFFF</td>
</tr>
<tr>
<td class="org-right">9</td>
<td class="org-left"><i>4</i></td>
<td class="org-right">1</td>
<td class="org-left">3 - 547</td>
</tr>
<tr>
<td class="org-right">10</td>
<td class="org-left"><i>1.1</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">11</td>
<td class="org-left"><i>-9</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">12</td>
<td class="org-left"><i>1001/345</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel AA</td>
</tr>
<tr>
<td class="org-right">13</td>
<td class="org-left"><i>1001/346</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel BB</td>
</tr>
<tr>
<td class="org-right">14</td>
<td class="org-left"><i>1001/345/1</i></td>
<td class="org-right">3</td>
<td class="org-left">ThirdLevel AA</td>
</tr>
<tr>
<td class="org-right">15</td>
<td class="org-left"><i>785/294/386/925</i></td>
<td class="org-right">4</td>
<td class="org-left">RandomEntry</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2016</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
